Currently, database administrators (DBAS) and application developers spend a large amount of time trying to tune poorly performing and resource intensive SQL statements (which is commonly referred to as bad sql). However, it is often a very challenging task. First, it requires a high level of expertise in several complex areas, such as query optimization and SQL design. Second, it is a time consuming process because each statement is unique and needs to be tuned individually. Third, it requires an intimate knowledge of the database (i.e., view definitions, indexes, table sizes, etc.) as well as the application (e.g. process flow, system load). Finally, the SQL tuning activity is a continuous task because the SQL workload and the database are always changing. As a result, tuning is often done on a trial and error basis, resulting in loss of productivity.
Often a SQL statement can be a high load SQL statement simply because it is badly written. This usually happens when there are different, but not semantically equivalent, ways to write a statement to produce same result. Knowing which of these alternate forms is most efficient in producing the query result is a difficult and daunting task for application developers since it requires both a deep knowledge about the properties of data they are querying as well as a very good understanding of the semantics and performance of SQL constructs.
To help DBAs and application developers overcome these challenges, several software companies have developed diagnostics tools that help identify SQL performance issues and suggest actions to fix them. However, these tools are not integrated with the database compiler, which is the system component that is most responsible for SQL performance. Indeed, these tools interpret the optimization information outside of the database to perform the tuning, so their tuning results are less robust and limited in scope. Moreover, they cannot directly tackle the internal challenges faced in producing an optimal execution plan.
The SQL Structure Analyzer component of the Automatic Tuning Optimizer performs what-if analysis to recognize missed query rewrite opportunities and makes SQL restructuring recommendations for the user to undertake.